
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

	public class getexcel1 {

		public void WriteExcel(File fileName,ResultSet rs) throws Exception{

			WritableWorkbook wwb = null;
			WritableSheet ws = null;
			try {
				wwb = Workbook.createWorkbook(fileName);
				ws = wwb.createSheet("sheet", 0);
			} catch (IOException e) {
				e.printStackTrace();
			}
  
  
			int i = 0;
			int j = 0;
    
			for (int k = 0; k < rs.getMetaData().getColumnCount(); k++) 
			{
				ws.addCell(new Label(k, 0, rs.getMetaData().getColumnName(k + 1)));
			}
			while (rs.next()) {
				for (int k = 0; k < rs.getMetaData().getColumnCount(); k++) {
					ws.addCell(new Label(k, j + i + 1, rs.getString(k + 1)));
				}
				i++;
			}
			wwb.write();
			wwb.close();
			rs.close();
		}
 
		public static void main(String[]args){
			File qfile = new File("D:/sendexcel/report1/sql1.txt");
			FileInputStream fis = null;
			try {
				fis = new FileInputStream(qfile);
			} catch (FileNotFoundException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			InputStreamReader reader = new InputStreamReader(fis);
			BufferedReader br = new BufferedReader(reader );
			String query = null;
			try {
				String line=null;
				while((line = br.readLine())!=null){
					query = query + line;
					System.out.println(query);
				}
			} catch (IOException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
	 
			getexcel mexel = new getexcel();
			jdbc myjdbc = new jdbc();
			myjdbc.getConnection();
			String sql = ""+query+"";
			Statement stm = null;
			ResultSet rs = null;
  
			File ffile = new File("D:/sendexcel/report1/subject1.txt");
			FileInputStream ffis = null;
			try {
				ffis = new FileInputStream(ffile);
			} catch (FileNotFoundException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			InputStreamReader freader = new InputStreamReader(ffis);
			BufferedReader fbr = new BufferedReader(freader );
			String filename = null;
			try {
				filename = fbr.readLine();
			} catch (IOException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			File newFile = new File("D:\\sendexcel\\"+filename+".xls");
			try {
				stm = myjdbc.getConnection().createStatement();
				rs = stm.executeQuery(sql);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				mexel.WriteExcel(newFile , rs);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				myjdbc.getConnection().close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
  
		}
	}